﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace ServiceThuVien
{
    public class SQLServerUpdate
    {
        SqlConnection cn;
        public SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

        private string GetConnection()
        {
            builder.ConnectionString = "server=RADO-PC\\SQLEXPRESS;user id=sa;" +
            "password= 123;initial catalog=QLTV";
            return builder.ConnectionString;
        }

        private void ConnectSQL(ref SqlConnection cn, string pName, ref SqlCommand cmd)
        {
            cn = new SqlConnection(GetConnection());
            string procName = pName;
            cmd = new SqlCommand();
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = cn;
        }

#region Update
        //Update Cuon Sach
        public int UpdateCuonSach(string MaCuonSach, string MaSach, int TinhTrang)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateCuonSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);
            cmd.Parameters.AddWithValue("@MaSach", MaSach);
            cmd.Parameters.AddWithValue("@TinhTrang", TinhTrang);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Update Dau Sach
        public int UpdateDauSach(string MaDauSach, string TenDauSach, string MaTheLoai)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateDauSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDauSach", MaDauSach);
            cmd.Parameters.AddWithValue("@TenDauSach", TenDauSach);
            cmd.Parameters.AddWithValue("@MaTheLoai", MaTheLoai);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Update Doc Gia
        public int UpdateDocGia(string MaDocGia, string MaLoaiDocGia, string HoTenDocGia, DateTime NgaySinh, string DiaChi, string Email, DateTime NgayLapThe, DateTime NgayHetHan)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateDocGia";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);
            cmd.Parameters.AddWithValue("@MaLoaiDocGia", MaLoaiDocGia);
            cmd.Parameters.AddWithValue("@HoTenDocGia", HoTenDocGia);
            cmd.Parameters.AddWithValue("@NgaySinh", NgaySinh);
            cmd.Parameters.AddWithValue("@DiaChi", DiaChi);
            cmd.Parameters.AddWithValue("@Email", Email);
            cmd.Parameters.AddWithValue("@NgayLapThe", NgayLapThe);
            cmd.Parameters.AddWithValue("@NgayHetHan", NgayHetHan);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Update Doc Gia - Tien No
        public int UpdateDocGia_TienNo(string MaDocGia, int TienNo)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateDocGia_TienNo";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);
            cmd.Parameters.AddWithValue("@TienNo", TienNo);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }
        
        //Update Doc Gia - MatKhau
        public int UpdateDocGia_MatKhau(string MaDocGia, string MatKhau)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateDocGia_MatKhau";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);
            cmd.Parameters.AddWithValue("@MatKhau", MatKhau);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Update Loai Doc Gia
        public int UpdateLoaiDocGia(string MaLoaiDocGia, string LoaiDocGia)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateLoaiDocGia";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaLoaiDocGia", MaLoaiDocGia);
            cmd.Parameters.AddWithValue("@LoaiDocGia", LoaiDocGia);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Update Sach
        public int UpdateSach(string MaSach, string MaDauSach, int NamXuatBan, string NhaXuatBan, int DonGia)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaSach", MaSach);
            cmd.Parameters.AddWithValue("@MaDauSach", MaDauSach);
            cmd.Parameters.AddWithValue("@NamXuatBan", NamXuatBan);
            cmd.Parameters.AddWithValue("@NhaXuatBan", NhaXuatBan);
            cmd.Parameters.AddWithValue("@DonGia", DonGia);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Update Tac Gia
        public int UpdateTacGia(string MaTacGia, string HoTenTacGia, string DiaChi)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateTacGia";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaTacGia", MaTacGia);
            cmd.Parameters.AddWithValue("@HoTenTacGia", HoTenTacGia);
            cmd.Parameters.AddWithValue("@DiaChi", DiaChi);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Update Tham So - Khoang cach nam xuat ban
        //public int UpdateThamSo_KhoangCachNamXB(int KhoangCachNamXB)
        //{
        //    cn = new SqlConnection(GetConnection());

        //    string procName = "UpdateThamSo_KhoangCachNamXB";
        //    SqlCommand cmd = new SqlCommand();

        //    ConnectSQL(ref cn, procName, ref cmd);

        //    cmd.Parameters.AddWithValue("@KhoangCachNamXB", KhoangCachNamXB);

        //    try
        //    {
        //        cn.Open();
        //        int num = cmd.ExecuteNonQuery();
        //        cn.Close();
        //        return num;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        ////Update Tham So - Ngay Muon Toi Da
        //public int UpdateThamSo_NgayMuonToiDa(int NgayMuonToiDa)
        //{
        //    cn = new SqlConnection(GetConnection());

        //    string procName = "UpdateThamSo_NgayMuonToiDa";
        //    SqlCommand cmd = new SqlCommand();

        //    ConnectSQL(ref cn, procName, ref cmd);

        //    cmd.Parameters.AddWithValue("@NgayMuonToiDa", NgayMuonToiDa);

        //    try
        //    {
        //        cn.Open();
        //        int num = cmd.ExecuteNonQuery();
        //        cn.Close();
        //        return num;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        ////Update Tham So - Sach Muon Toi Da
        //public int UpdateThamSo_SachMuonToiDa(int SachMuonToiDa)
        //{
        //    cn = new SqlConnection(GetConnection());

        //    string procName = "UpdateThamSo_SachMuonToiDa";
        //    SqlCommand cmd = new SqlCommand();

        //    ConnectSQL(ref cn, procName, ref cmd);

        //    cmd.Parameters.AddWithValue("@SachMuonToiDa", SachMuonToiDa);

        //    try
        //    {
        //        cn.Open();
        //        int num = cmd.ExecuteNonQuery();
        //        cn.Close();
        //        return num;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        ////Update Tham So - Tien phat
        //public int UpdateThamSo_TienPhat(int TienPhat)
        //{
        //    cn = new SqlConnection(GetConnection());

        //    string procName = "UpdateThamSo_TienPhat";
        //    SqlCommand cmd = new SqlCommand();

        //    ConnectSQL(ref cn, procName, ref cmd);

        //    cmd.Parameters.AddWithValue("@TienPhat", TienPhat);

        //    try
        //    {
        //        cn.Open();
        //        int num = cmd.ExecuteNonQuery();
        //        cn.Close();
        //        return num;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        ////Update Tham So - Tuoi Toi Da
        //public int UpdateThamSo_TuoiToiDa(int TuoiToiDa)
        //{
        //    cn = new SqlConnection(GetConnection());

        //    string procName = "UpdateThamSo_TuoiToiDa";
        //    SqlCommand cmd = new SqlCommand();

        //    ConnectSQL(ref cn, procName, ref cmd);

        //    cmd.Parameters.AddWithValue("@TuoiToiDa", TuoiToiDa);

        //    try
        //    {
        //        cn.Open();
        //        int num = cmd.ExecuteNonQuery();
        //        cn.Close();
        //        return num;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        ////Update Tham So - Tuoi Toi Thieu
        //public int UpdateThamSo_TuoiToiThieu(int TuoiToiThieu)
        //{
        //    cn = new SqlConnection(GetConnection());

        //    string procName = "UpdateThamSo_TuoiToiThieu";
        //    SqlCommand cmd = new SqlCommand();

        //    ConnectSQL(ref cn, procName, ref cmd);

        //    cmd.Parameters.AddWithValue("@TuoiToiThieu", TuoiToiThieu);

        //    try
        //    {
        //        cn.Open();
        //        int num = cmd.ExecuteNonQuery();
        //        cn.Close();
        //        return num;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        ////Update Tham So - Thoi Han The
        //public int UpdateThamSo_ThoiHanThe(int ThoiHanThe)
        //{
        //    cn = new SqlConnection(GetConnection());

        //    string procName = "UpdateThamSo_ThoiHanThe";
        //    SqlCommand cmd = new SqlCommand();

        //    ConnectSQL(ref cn, procName, ref cmd);

        //    cmd.Parameters.AddWithValue("@ThoiHanThe", ThoiHanThe);

        //    try
        //    {
        //        cn.Open();
        //        int num = cmd.ExecuteNonQuery();
        //        cn.Close();
        //        return num;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        //Update Tham So - The Loai

        public int UpdateTheLoai(string MaTheLoai, string TenTheLoai)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateTheLoai";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaTheLoai", MaTheLoai);
            cmd.Parameters.AddWithValue("@TenTheLoai", TenTheLoai);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int UpdateDoiMatKhau(string MaDocGia, int DoiMatKhau)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateDoiMatKhau";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);
            cmd.Parameters.AddWithValue("@DoiMatKhau", DoiMatKhau);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int UpdateMatKhau(string MaDocGia, string MatKhau)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateMatKhau";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);
            cmd.Parameters.AddWithValue("@MatKhau", MatKhau);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int UpdateTongTien_PhieuNhap(string MaPhieuNhap, int TongTien)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateTongTien_PhieuNhap";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaPhieuNhap", MaPhieuNhap);
            cmd.Parameters.AddWithValue("@TongTien", TongTien);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int UpdateSoLuong_Sach(string MaSach, int SoLuong)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateSoLuong_Sach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaSach", MaSach);
            cmd.Parameters.AddWithValue("@SoLuong", SoLuong);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int UpdateTinhTrangCuonSach(string MaCuonSach, int TinhTrang)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateTinhTrangCuonSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);
            cmd.Parameters.AddWithValue("@TinhTrang", TinhTrang);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int UpdateSoSachMuon(string MaPhieuMuon, int SoSachMuon)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateSoSachMuon";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaPhieuMuon", MaPhieuMuon);
            cmd.Parameters.AddWithValue("@SoSachMuon", SoSachMuon);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int UpdateGiaTriThamSo(string TenThamSo, int GiaTri)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateGiaTriThamSo";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@TenThamSo", TenThamSo);
            cmd.Parameters.AddWithValue("@GiaTri", GiaTri);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int UpdateSoSachDangMuon_DocGia(string MaDocGia, int SoSachDangMuon)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateSoSachDangMuon_DocGia";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);
            cmd.Parameters.AddWithValue("@SoSachDangMuon", SoSachDangMuon);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return -1;
            }
        }

        public int UpdateTienPhatKyNay(string MaPhieuTra, int TienPhatKyNay)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateTienPhatKyNay";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaPhieuTra", MaPhieuTra);
            cmd.Parameters.AddWithValue("@TienPhatKyNay", TienPhatKyNay);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return -1;
            }
        }

        public int UpdateTienNoDocGia(string MaDocGia, int TienNo)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateTienNoDocGia";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);
            cmd.Parameters.AddWithValue("@TienNo", TienNo);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return -1;
            }
        }

        public int UpdateTinhTrangPhieuMuon(string MaCuonSach, string MaPhieuMuon, int TinhTrangPhieu)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateTinhTrangPhieuMuon";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);
            cmd.Parameters.AddWithValue("@MaPhieuMuon", MaPhieuMuon);
            cmd.Parameters.AddWithValue("@TinhTrangPhieu", TinhTrangPhieu);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return -1;
            }
        }

        public int UpdateDoiMatKhau_NhanVien(string MaNhanVien, int DoiMatKhau)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateDoiMatKhau_NhanVien";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaNhanVien", MaNhanVien);
            cmd.Parameters.AddWithValue("@DoiMatKhau", DoiMatKhau);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int UpdateMatKhau_NhanVien(string MaNhanVien, string MatKhau)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateMatKhau_NhanVien"; 
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaNhanVien", MaNhanVien);
            cmd.Parameters.AddWithValue("@MatKhau", MatKhau);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int UpdateNhanVien(string MaNhanVien, string MaLoaiNhanVien, string HoTenNhanVien)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateNhanVien";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaNhanVien", MaNhanVien);
            cmd.Parameters.AddWithValue("@MaLoaiNhanVien", MaLoaiNhanVien);
            cmd.Parameters.AddWithValue("@HoTenNhanVien", HoTenNhanVien);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int UpdateNgayHetHan(string MaDocGia, DateTime NgayHetHan)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "UpdateNgayHetHan";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);
            cmd.Parameters.AddWithValue("@NgayHetHan", NgayHetHan);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }
#endregion
    }
}